SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `news` DEFAULT CHARACTER SET latin1 ;
USE `news` ;

-- -----------------------------------------------------
-- Table `news`.`usuario`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `news`.`usuario` (
  `login` VARCHAR(70) NOT NULL ,
  `nome` VARCHAR(70) NOT NULL ,
  `senha` VARCHAR(15) NOT NULL DEFAULT '123' ,
  `status` BIT(1) NOT NULL DEFAULT b'0' COMMENT '1 = ATIVO; 0 = INATIVO' ,
  `Perfil` VARCHAR(70) NOT NULL ,
  `email` VARCHAR(70) NULL DEFAULT NULL ,
  `telefone` VARCHAR(20) NULL DEFAULT NULL ,
  `dataNasc` TIMESTAMP NULL DEFAULT NULL ,
  `sexo` CHAR(1) NULL DEFAULT NULL ,
  PRIMARY KEY (`login`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `news`.`categoria`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `news`.`categoria` (
  `idCategoria` INT(11) NOT NULL AUTO_INCREMENT ,
  `dataCriacao` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  `descricao` VARCHAR(50) NOT NULL ,
  `status` BIT(1) NOT NULL DEFAULT b'0' COMMENT '1 = ATIVO; 0 = INATIVO' ,
  `nota` VARCHAR(3000) NULL DEFAULT NULL ,
  `criadoPor` VARCHAR(70) NULL DEFAULT NULL ,
  `categoriaPai` INT(11) NULL DEFAULT NULL ,
  PRIMARY KEY (`idCategoria`) ,
  INDEX `fk_CATEGORIA_USUARIO1` (`criadoPor` ASC) ,
  INDEX `fk_Categoria_Categoria1` (`categoriaPai` ASC) ,
  CONSTRAINT `fk_Categoria_Categoria1`
    FOREIGN KEY (`categoriaPai` )
    REFERENCES `news`.`categoria` (`idCategoria` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_CATEGORIA_USUARIO1`
    FOREIGN KEY (`criadoPor` )
    REFERENCES `news`.`usuario` (`login` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `news`.`fonte`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `news`.`fonte` (
  `idFonte` INT(11) NOT NULL AUTO_INCREMENT ,
  `descricao` VARCHAR(50) NOT NULL ,
  PRIMARY KEY (`idFonte`) )
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `news`.`noticia`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `news`.`noticia` (
  `idNoticia` INT(11) NOT NULL AUTO_INCREMENT ,
  `autor` VARCHAR(70) NULL DEFAULT NULL ,
  `dataCriacao` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  `dataNoticia` TIMESTAMP NULL DEFAULT NULL ,
  `status` BIT(1) NOT NULL DEFAULT b'0' COMMENT '1 = ATIVO; 0 = INATIVO' ,
  `texto` VARCHAR(50000) NOT NULL ,
  `titulo` VARCHAR(300) NOT NULL ,
  `criadoPor` VARCHAR(70) NOT NULL ,
  `idFonte` INT(11) NOT NULL ,
  `idCategoria` INT(11) NOT NULL ,
  `imagem` VARCHAR(400) NULL DEFAULT NULL ,
  `subTitulo` VARCHAR(300) NULL DEFAULT NULL ,
  `url` VARCHAR(1000) NULL DEFAULT NULL ,
  PRIMARY KEY (`idNoticia`) ,
  INDEX `fk_NOTICIA_USUARIO1` (`criadoPor` ASC) ,
  INDEX `fk_NOTICIA_FONTE1` (`idFonte` ASC) ,
  INDEX `fk_NOTICIA_CATEGORIA1` (`idCategoria` ASC) ,
  CONSTRAINT `fk_NOTICIA_CATEGORIA1`
    FOREIGN KEY (`idCategoria` )
    REFERENCES `news`.`categoria` (`idCategoria` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_NOTICIA_FONTE1`
    FOREIGN KEY (`idFonte` )
    REFERENCES `news`.`fonte` (`idFonte` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_NOTICIA_USUARIO1`
    FOREIGN KEY (`criadoPor` )
    REFERENCES `news`.`usuario` (`login` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 726
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `news`.`acessonoticia`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `news`.`acessonoticia` (
  `idNoticia` INT(11) NOT NULL ,
  `login` VARCHAR(70) NULL DEFAULT NULL ,
  `idAcesso` INT(11) NOT NULL AUTO_INCREMENT ,
  `dataCriacao` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  `ip` VARCHAR(50) NOT NULL ,
  PRIMARY KEY (`idAcesso`) ,
  INDEX `fk_Noticia_has_Usuario_Usuario1` (`login` ASC) ,
  INDEX `fk_Noticia_has_Usuario_Noticia1` (`idNoticia` ASC) ,
  CONSTRAINT `fk_Noticia_has_Usuario_Noticia1`
    FOREIGN KEY (`idNoticia` )
    REFERENCES `news`.`noticia` (`idNoticia` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Noticia_has_Usuario_Usuario1`
    FOREIGN KEY (`login` )
    REFERENCES `news`.`usuario` (`login` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 86
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `news`.`enquete`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `news`.`enquete` (
  `idEnquete` INT(11) NOT NULL AUTO_INCREMENT ,
  `questao` VARCHAR(300) NOT NULL ,
  `datacriacao` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  `status` BIT(1) NOT NULL DEFAULT b'0' ,
  PRIMARY KEY (`idEnquete`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `news`.`alternativaenquete`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `news`.`alternativaenquete` (
  `alternativa` SMALLINT(6) NOT NULL ,
  `descricao` VARCHAR(300) NOT NULL ,
  `idEnquete` INT(11) NOT NULL ,
  PRIMARY KEY (`alternativa`, `idEnquete`) ,
  INDEX `fk_AlternativaEnquete_Enquete1` (`idEnquete` ASC) ,
  CONSTRAINT `fk_AlternativaEnquete_Enquete1`
    FOREIGN KEY (`idEnquete` )
    REFERENCES `news`.`enquete` (`idEnquete` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `news`.`anuncio`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `news`.`anuncio` (
  `idAnuncio` INT(11) NOT NULL AUTO_INCREMENT ,
  `nome` VARCHAR(100) NOT NULL ,
  `email` VARCHAR(70) NOT NULL ,
  `cnpj` VARCHAR(20) NOT NULL ,
  `telefone` VARCHAR(20) NOT NULL ,
  `outroTelefone` VARCHAR(20) NULL DEFAULT NULL ,
  `site` VARCHAR(100) NULL DEFAULT NULL ,
  `dataCriacao` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  `flagEnvioEmail` BIT(1) NULL DEFAULT NULL ,
  PRIMARY KEY (`idAnuncio`) )
ENGINE = InnoDB
AUTO_INCREMENT = 32
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `news`.`auditoria`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `news`.`auditoria` (
  `idAuditoria` INT(11) NOT NULL AUTO_INCREMENT ,
  `data` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  `atributoAlterado` VARCHAR(70) NOT NULL ,
  `idObjeto` INT(11) NOT NULL ,
  `tipoObjeto` VARCHAR(50) NOT NULL ,
  `valorAnt` VARCHAR(3000) NULL DEFAULT NULL ,
  `valorNovo` VARCHAR(3000) NULL DEFAULT NULL ,
  `usuario` VARCHAR(70) NULL DEFAULT NULL ,
  PRIMARY KEY (`idAuditoria`) ,
  INDEX `OBJETO` (`idObjeto` ASC, `tipoObjeto` ASC) ,
  INDEX `fk_AUDITORIA_USUARIO` (`usuario` ASC) ,
  CONSTRAINT `fk_AUDITORIA_USUARIO`
    FOREIGN KEY (`usuario` )
    REFERENCES `news`.`usuario` (`login` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `news`.`usuarioandroid`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `news`.`usuarioandroid` (
  `idUsuarioAndroid` INT(11) NOT NULL AUTO_INCREMENT ,
  `dataCriacao` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY (`idUsuarioAndroid`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `news`.`usuariotipo`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `news`.`usuariotipo` (
  `login` VARCHAR(70) NULL DEFAULT NULL ,
  `idUsuarioAndroid` INT(11) NULL DEFAULT NULL ,
  `idUsuarioTipo` INT(11) NOT NULL ,
  PRIMARY KEY (`idUsuarioTipo`) ,
  INDEX `fk_Usuario_has_UsuarioAndroid_UsuarioAndroid1` (`idUsuarioAndroid` ASC) ,
  INDEX `fk_Usuario_has_UsuarioAndroid_Usuario1` (`login` ASC) ,
  CONSTRAINT `fk_Usuario_has_UsuarioAndroid_Usuario1`
    FOREIGN KEY (`login` )
    REFERENCES `news`.`usuario` (`login` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Usuario_has_UsuarioAndroid_UsuarioAndroid1`
    FOREIGN KEY (`idUsuarioAndroid` )
    REFERENCES `news`.`usuarioandroid` (`idUsuarioAndroid` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `news`.`categoriausuario`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `news`.`categoriausuario` (
  `idCategoriaUsuario` INT(11) NOT NULL AUTO_INCREMENT ,
  `data` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  `status` BIT(1) NOT NULL DEFAULT b'0' ,
  `idCategoria` INT(11) NOT NULL ,
  `dataInativacao` TIMESTAMP NULL DEFAULT NULL ,
  `idUsuarioTipo` INT(11) NULL DEFAULT NULL ,
  `flagRecebeEmail` BIT(1) NULL DEFAULT NULL ,
  `idUsuario` VARCHAR(70) NOT NULL ,
  PRIMARY KEY (`idCategoriaUsuario`) ,
  INDEX `fk_CATEGORIAUSUARIO_CATEGORIA1` (`idCategoria` ASC) ,
  INDEX `fk_CategoriaUsuario_UsuarioTipo1` (`idUsuarioTipo` ASC) ,
  INDEX `fk_CategoriaUsuario_usuario` (`idUsuario` ASC) ,
  CONSTRAINT `fk_CATEGORIAUSUARIO_CATEGORIA1`
    FOREIGN KEY (`idCategoria` )
    REFERENCES `news`.`categoria` (`idCategoria` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_CategoriaUsuario_usuario`
    FOREIGN KEY (`idUsuario` )
    REFERENCES `news`.`usuario` (`login` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_CategoriaUsuario_UsuarioTipo1`
    FOREIGN KEY (`idUsuarioTipo` )
    REFERENCES `news`.`usuariotipo` (`idUsuarioTipo` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `news`.`comentario`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `news`.`comentario` (
  `idComentario` INT(11) NOT NULL AUTO_INCREMENT ,
  `nome` VARCHAR(70) NULL DEFAULT NULL ,
  `desComentario` VARCHAR(4000) NOT NULL ,
  `dataCriacao` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  `login` VARCHAR(70) NULL DEFAULT NULL ,
  `idNoticia` INT(11) NOT NULL ,
  PRIMARY KEY (`idComentario`) ,
  INDEX `fk_Comentario_Usuario1` (`login` ASC) ,
  INDEX `fk_Comentario_Noticia1` (`idNoticia` ASC) ,
  CONSTRAINT `fk_Comentario_Noticia1`
    FOREIGN KEY (`idNoticia` )
    REFERENCES `news`.`noticia` (`idNoticia` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Comentario_Usuario1`
    FOREIGN KEY (`login` )
    REFERENCES `news`.`usuario` (`login` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `news`.`contato`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `news`.`contato` (
  `idContato` INT(11) NOT NULL AUTO_INCREMENT ,
  `mensagem` VARCHAR(400) NOT NULL ,
  `nome` VARCHAR(45) NOT NULL ,
  `email` VARCHAR(70) NOT NULL ,
  `flagEnvioEmail` BIT(1) NULL DEFAULT NULL ,
  PRIMARY KEY (`idContato`) )
ENGINE = InnoDB
AUTO_INCREMENT = 15
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `news`.`emailindicacao`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `news`.`emailindicacao` (
  `idEmailIndicacao` INT(11) NOT NULL AUTO_INCREMENT ,
  `data` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  `email` VARCHAR(70) NOT NULL ,
  `idNoticia` INT(11) NOT NULL ,
  `login` VARCHAR(70) NULL DEFAULT NULL ,
  PRIMARY KEY (`idEmailIndicacao`) ,
  INDEX `fk_EmailIndicacao_Noticia1` (`idNoticia` ASC) ,
  INDEX `fk_EmailIndicacao_Usuario1` (`login` ASC) ,
  CONSTRAINT `fk_EmailIndicacao_Noticia1`
    FOREIGN KEY (`idNoticia` )
    REFERENCES `news`.`noticia` (`idNoticia` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_EmailIndicacao_Usuario1`
    FOREIGN KEY (`login` )
    REFERENCES `news`.`usuario` (`login` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `news`.`respostaenquete`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `news`.`respostaenquete` (
  `idRespostaEnquete` INT(11) NOT NULL AUTO_INCREMENT ,
  `dataResposta` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
  `alternativa` SMALLINT(6) NOT NULL ,
  `idEnquete` INT(11) NOT NULL ,
  PRIMARY KEY (`idRespostaEnquete`) ,
  INDEX `fk_RespostaEnquete_AlternativaEnquete1` (`alternativa` ASC, `idEnquete` ASC) ,
  CONSTRAINT `fk_RespostaEnquete_AlternativaEnquete1`
    FOREIGN KEY (`alternativa` , `idEnquete` )
    REFERENCES `news`.`alternativaenquete` (`alternativa` , `idEnquete` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
